<?php

//Forbindelse til databasen

class connect {

    /**
     * Forbindelsen til databasen
     *
     * @var object
     */
    private $conn;

    /**
     * Instans af forbindelsen til databasen
     *
     * @var object
     */
    private static $instance;

    /**
     * Konstrukt�r, opretter forbindelse til databasen
     *
     */
    private function __construct() {

        $this->conn = new mysqli("sdca2bb0", "himmerland", "12qwaszx", "test", 3306);
        
    }

    /**
     * Instans af database objektet
     *
     * @return object
     */
    public static function getInstance() {

        if (is_null(self::$instance)) {
            self::$instance = new connect();
        }

        return self::$instance;
    }

    public function close() {
        self::$instance->conn->close();
        self::$instance = null;
    }

    /**
     * V�lger database og returnere et databasetabel object
     *
     * @param String $db
     * @return Object
     */
    public function conn($db) {
        $this->conn->select_db($db);
        return $this->conn;
    }

}

/*

  ActiveRecords klassen er skrevet af Rasmus Jensen 2008.
  ActiveRecords klassen er bygget over designprincippet Active Record Pattern.
  ActiveRecords er en for�ldreklasse til h�ndtering af foresp�rgsel til databaser.
  ActiveRecords skal nedarves af andre mere specifikke klasse til h�ndtering af databasen.

  Alle data k�res gennemk�res mysqli_real_escape_string inden de inds�ttes i databasen.

 */

/**
 * Abstrakt klasse, ActiveRecords
 *
 */
abstract class ActiveRecords {

    /**
     * Databaseforbindelsen
     *
     * @var Object
     */
    private $conn = null;

    /**
     * Den valgte tabel
     *
     * @var String
     */
    private $table = null;

    /**
     * En r�kke, der kan inds�ttes i databasen
     *
     * @var Array
     */
    public $row = null;

    /**
     * Fejlkode
     *
     * @var string
     */
    public $error = "";

    /**
     * Database navn
     *
     * @var string
     */
    private $database = null;

    /**
     * Oprette ny forbindelse til en tabel i databasen og s�tter private $conn
     *
     * @param String $database
     * @param String $table
     * @return bool
     */
    protected function connect($database, $table="") {
        $this->table = $table;
        $this->database = $database;

        $conn = connect::getInstance()->conn($database);
        if (!$conn) {
            return false;
        } else {
            $this->conn = $conn;
            return true;
        }
    }

    public function disconnect() {

        $conn = connect::getInstance();
        $conn->close();
    }

    /**
     * Destrukt�r, rydder op i hukommelsen
     *
     */
    function __destruct() {
        unset($this->conn);
    }

    /*
      protected function encode($array){

      $keys = array_keys($array);

      foreach ($keys as $k){
      if (!is_numeric($array[$k])){
      //$array[$k] = htmlspecialchars($array[$k],ENT_QUOTES);
      }
      }

      return $array;
      }
     */

    private function seterror() {
        $this->error = $this->conn->error;
    }

    /**
     * Intern funktion til dekodning af escapede karaterer i et array.
     *
     * @param Array $array
     * @return Array
     */
    protected function decode($array) {

        if (is_array($array)) {

            $keys = array_keys($array);

            foreach ($keys as $k) {
                if (!is_numeric($array[$k])) {
                    $array[$k] = stripcslashes($array[$k]);
                }
            }

            return $array;
        } else {
            return false;
        }
    }

    /**
     * Laver en SELECT foresp�rgsel til en database, returnere et array.
     * Bem�rk at indeholdet af kolonner med deltitler _html htmldecodes.
     *
     * @param string $select
     * @param string $where
     * @param string $from
     * @param string $orderby
     * @param int $limit
     * @return array
     */
    public function get($select="*", $from="", $where="", $orderby="", $limit=0, $showsql=false) {

        $sql = "SELECT $select ";
        if ($from == "") {
            $from = $this->table;
        }
        if (strstr($from, "`")) {
            $sql = $sql . "FROM $from ";
        } else {
            $sql = $sql . "FROM `$from` ";
        }

        if ($where != "") {
            $sql = $sql . "WHERE ($where) ";
        }

        if ($orderby != "") {
            $sql = $sql . "ORDER BY $orderby ";
        }

        if ($limit > 0 || stristr($limit, ",")) {
            $sql = $sql . "LIMIT $limit";
        }

        if ($showsql) {
            echo $sql;
        }


        $result = $this->conn->query($sql);

        if (!$result) {
            $this->seterror();
            return false;
        }

        if ($limit != 1) {
            $array = null;
            While ($row = $result->fetch_array(MYSQLI_ASSOC)) {

                $row = $this->decode($row);
                $array[] = $row;
            }
            if (is_array($array)) {
                return $array;
            } else {
                return false;
            }
        } else {
            $row = $result->fetch_array(MYSQLI_ASSOC);
            $row = $this->decode($row);
            return $row;
        }
    }
    
    
    
    
    
        /**
     * Laver en SELECT foresp�rgsel til en database, returnere et array.
     * Bem�rk!!! Felter decodes ikke (brugbart, n�r man skal hente json/xml fra en felt.
     *
     * @param string $select
     * @param string $where
     * @param string $from
     * @param string $orderby
     * @param int $limit
     * @return array
     */
    public function getRaw($select="*", $from="", $where="", $orderby="", $limit=0, $showsql=false) {

        $sql = "SELECT $select ";
        if ($from == "") {
            $from = $this->table;
        }
        if (strstr($from, "`")) {
            $sql = $sql . "FROM $from ";
        } else {
            $sql = $sql . "FROM `$from` ";
        }

        if ($where != "") {
            $sql = $sql . "WHERE ($where) ";
        }

        if ($orderby != "") {
            $sql = $sql . "ORDER BY $orderby ";
        }

        if ($limit > 0 || stristr($limit, ",")) {
            $sql = $sql . "LIMIT $limit";
        }

        if ($showsql) {
            echo $sql;
        }


        $result = $this->conn->query($sql);

        if (!$result) {
            $this->seterror();
            return false;
        }

        if ($limit != 1) {
            $array = null;
            While ($row = $result->fetch_array(MYSQLI_ASSOC)) {

                $array[] = $row;
            }
            if (is_array($array)) {
                return $array;
            } else {
                return false;
            }
        } else {
            $row = $result->fetch_array(MYSQLI_ASSOC);
            return $row;
        }
    }
    
    

    /**
     * Henter kolonner fra tabel via. SHOW COLUMNS
     *
     * @param string $from
     * @return array
     */
    public function get_columns($from="") {

        $sql = "SHOW COLUMNS ";
        if ($from == "") {
            $from = $this->table;
        }
        if (strstr($from, "`")) {
            $sql = $sql . "FROM $from ";
        } else {
            $sql = $sql . "FROM `$from` ";
        }


        $result = $this->conn->query($sql);

        if (!$result) {
            $this->seterror();
            return false;
        }


        While ($row = $result->fetch_array(MYSQLI_ASSOC)) {

            $row = $this->decode($row);
            $array[] = $row;
        }
        return $array;
    }

    /**
     * Henter kolonner fra en tabel (kr�ver at der er data i tabellen)
     * !!! BRUG get_columns istedet
     *
     * @param string $from
     * @return array
     */
    public function get_fields($from="") {
        $cols = $this->get_columns($from);
        if (is_array($cols)) {
            foreach ($cols as $c) {
                $array[] = $c['Field'];
            }
            return $array;
        } else {
            $this->seterror();
            return false;
        }
    }

    /**
     * T�ller antal r�kker, returnere et tal.
     *
     * @param string $where
     * @param string $from
     * @return int
     */
    public function count($where, $from="") {

        $sql = "SELECT COUNT(*) AS num ";
        if ($from == "") {
            $from = $this->table;
        }
        $sql = $sql . "FROM `$from` ";
        if ($where != "") {
            $sql = $sql . "WHERE ($where)";
        }

        $result = $this->conn->query($sql);
        if ($result == false) {
            return 0;
        } else {
            $row = $result->fetch_array(MYSQLI_ASSOC);
            return $row['num'];
        }
    }

    
  
    
    
    
    /**
     * Returnere forbindelsen til databasen
     *
     * @return object
     */
    public function get_conn() {
        return $this->conn;
    }

    /**
     * Henter TABLE STATUS
     *
     * @param string $table
     * @return Array eller false
     */
    public function tablestatus($table="") {
        if ($table == "") {
            $table = $this->table;
        }

        $result = $this->conn->query("SHOW TABLE STATUS FROM " . $this->database . " WHERE `Name`='$table'");

        $row = $result->fetch_array(MYSQLI_ASSOC);
        $row = $this->decode($row);
        return $row;
    }

    public function startTransaction() {
        return $this->conn->autocommit(false);
    }

    public function commit() {

        $debug = $this->conn->commit();
        $this->conn->autocommit(true);
        return $debug;
    }

    public function rollback() {
        $debug = $this->conn->rollback();
        $this->conn->autocommit(true);
        return $debug;
    }

    
     public function showCreateTable($table){
            $result = $this->conn->query("SHOW CREATE TABLE `$table`");

            		$numrows = $result->num_rows;

			if ($numrows == 1)
			{
				While ($row = $result->fetch_array(MYSQLI_ASSOC)) {

					$row = $this->decode($row);
					$array[] = $row;

				}
				return $array[0]['Create Table'];
			}else{
                            return false;
                        }
        
        }
        
        
 
        
    
    
    
    /**
     * Laver en SQL foresp�rgsel, returnere et resultat-objekt eller et array i tilf�det SELECT, medmindre forceobject er true.
     * Bem�rk at indeholdet af kolonner med deltitler _html htmldecodes.
     *
     * @param string $sql
     * @param bool $forceobject
     * @return result object eller array i tilf�ldet SELECT (medmindre forceobject er true)
     */
    public function sqlexec($sql, $forceobject=false) {
        $result = $this->conn->query($sql);

        if (stripos($sql, "select") < 2 && stristr($sql, "select") != false && $forceobject == false) {

            $numrows = $result->num_rows;

            if ($numrows > 1) {
                While ($row = $result->fetch_array(MYSQLI_ASSOC)) {

                    $row = $this->decode($row);
                    $array[] = $row;
                }
                return $array;
            } elseif ($numrows == 1) {
                // Kun returner single array hvis LIMIT 1
                if (stristr($slq, "LIMIT 1")) {
                    $row = $result->fetch_array(MYSQLI_ASSOC);
                    $row = $this->decode($row);
                    return $row;
                } else {
                    $row[0] = $result->fetch_array(MYSQLI_ASSOC);
                    $row[0] = $this->decode($row[0]);
                    return $row;
                }
            } else {
                $this->seterror();
                return false;
            }
        }

        return $result;
    }

    /**
     * Laver en SQL foresp�rgsel (designet til at kalde STORED PROCEDURES)
     *
     * @param string $sql
     * @param bool $limit1
     * @return Array
     */
    public function sqlexec_toArray($sql, $limit1 = false) {
        // Danner selvst�ndigt mysqli object, og sletter det igen..
        // Dette er n�dvendigt, for at STORED PROCEDURE kan returnere result-set, flere gange i tr�k ...
        $mysqli = new mysqli("10.234.43.174", "himmerland", "12qwaszx", $this->database);
        $result = $mysqli->query($sql);
        unset($mysqli);

        $numrows = $result->num_rows;

        if ($numrows > 1) {
            While ($row = $result->fetch_array(MYSQLI_ASSOC)) {

                $row = $this->decode($row);
                $array[] = $row;
            }

            if ($limit1) {
                return $array[0];
            } else {
                return $array;
            }
        } elseif ($numrows == 1) {
            // Kun returner single array hvis LIMIT 1
            if ($limit1) {
                $row = $result->fetch_array(MYSQLI_ASSOC);
                $row = $this->decode($row);
                return $row;
            } else {
                $row[0] = $result->fetch_array(MYSQLI_ASSOC);
                $row[0] = $this->decode($row[0]);
                return $row;
            }
        } else {
            return false;
        }
    }

    /**
     * Laver en multi-SQL foresp�rgsel, returnere et result-objekt.
     *
     * @param string $sql
     * @return result object.
     */
    public function sql_multi_exec($sql) {
        $result = $this->conn->multi_query($sql);
        $numrows = $result->num_rows;

        if ($numrows > 1) {
            While ($row = $result->fetch_array(MYSQLI_ASSOC)) {

                $row = $this->decode($row);
                $array[] = $row;
            }
            return $array;
        } elseif ($numrows == 1) {
            // Kun returner single array hvis LIMIT 1
            if (stristr($slq, "LIMIT 1")) {
                $row = $result->fetch_array(MYSQLI_ASSOC);
                $row = $this->decode($row);
                return $row;
            } else {
                $row[0] = $result->fetch_array(MYSQLI_ASSOC);
                $row[0] = $this->decode($row[0]);
                return $row;
            }
        } else {
            $this->seterror();
            return false;
        }
    }

    /**
     * Sletter data fra tabellen.
     *
     * @param string $where
     * @return bool
     */
    public function delete($where) {

        $sql = "DELETE FROM `" . $this->table . "`
				WHERE $where";
        $result = $this->conn->query($sql);
        return $result;
    }

    /**
     * Sletter data i tabellen udfra id.
     *
     * @param int $id
     * @return bool
     */
    public function delete_by_id($id) {

        $sql = "DELETE FROM `" . $this->table . "`
				WHERE `id`='$id'";
        $result = $this->conn->query($sql);
        return $result;
    }

    public function escapestring($s) {
        return $this->conn->real_escape_string($s);
    }

    /**
     * Inds�tter v�rdier i databasen udfra et array.
     *
     * @param array $array
     * @return bool
     */
    protected function insert($array, $table="", $showsql=false, $utf8=false, $returnid=false, $returnsql_noexec=false) {
        //$array = $this->encode($array);
        if ($utf8) {
            $this->conn->set_charset('utf8');
        }

        $keys = array_keys($array);
        if ($table == "") {
            $sql = "INSERT INTO `" . $this->table . "`(";
        } else {
            $sql = "INSERT INTO `" . $table . "`(";
        }

        foreach ($keys as $k) {
            $sql = $sql . "`$k`,";
        }
        $len = strlen($sql);
        $sql = substr($sql, 0, $len - 1);
        $sql = $sql . ") VALUES (";

        foreach ($keys as $k) {

            $sql = $sql . "'" . $this->conn->real_escape_string($array[$k]) . "',";
        }

        $len = strlen($sql);
        $sql = substr($sql, 0, $len - 1);
        $sql = $sql . ")";

        if ($showsql) {
            echo $sql;
        }

        if ($returnsql_noexec) {
            return $sql;
        } else {
            $result = $this->conn->query($sql);
            if (!$result) {
                $this->seterror();
            }
            if ($returnid) {
                return $this->conn->insert_id;
            } else {
                return $result;
            }
        }
    }

    /**
     * Opdaterer v�rdier i databasen udfra et array og et id.
     *
     * @param int $id
     * @param array $array
     * @return bool
     */
    public function update_by_id($id, $array, $showsql=false, $utf8=false) {
        //$array = $this->encode($array);

        if ($utf8) {
            $this->conn->set_charset('utf8');
        }


        $keys = array_keys($array);

        $sql = "UPDATE `" . $this->table . "` SET ";

        foreach ($keys as $k) {

            $sql = $sql . "`" . $k . "`='" . $this->conn->real_escape_string($array[$k]) . "',";
        }

        $len = strlen($sql);
        $sql = substr($sql, 0, $len - 1);

        $sql = $sql . " WHERE `id`='$id'";
        if ($showsql) {
            echo "<br><br>" . $sql . "<br><br>";
        }
        $result = $this->conn->query($sql);

        return $result;
    }

    /**
     * Opdaterer v�rdier i databasen udfra et array og en where clause.
     *
     * @param string $where
     * @param array $array
     * @return bool
     */
    public function update($where, $array, $utf8=false, $returnsql_noexec=false) {
        //$array = $this->encode($array);
        if ($utf8) {
            $this->conn->set_charset('utf8');
        }

        $keys = array_keys($array);

        $sql = "UPDATE `" . $this->table . "` SET ";

        foreach ($keys as $k) {

            $sql = $sql . "`" . $k . "`='" . $this->conn->real_escape_string($array[$k]) . "',";
        }

        $len = strlen($sql);
        $sql = substr($sql, 0, $len - 1);

        $sql = $sql . " WHERE $where";

        if ($returnsql_noexec) {
            return $sql;
        } else {
            $result = $this->conn->query($sql);
            return $result;
        }
    }

    /**
     * Gemmer indholdet af et array eller row i databasen
     *
     * @param array $array
     * @param string $table
     * @param bool $showsql
     * @param bool $utf8
     * @return bool
     */
    public function saverow($array="", $table="", $showsql=false, $utf8=false, $returnid=false, $returnsql_noexec=false) {

        if ($array != "") {
            $this->array_to_row($array);
        }

        // SQL foresp�rgsel, for at f� kolonner, der eksisterer i tabellen.
        if ($table == "") {
            $table = $this->table;
        }

        $proved_row = null;
        $fields = null;
        $columns = $this->get_columns($table);
        if (is_array($columns)) {
            foreach ($columns as $c) {
                $fields[] = $c['Field'];
            }
        } else {
            return false;
        }


        // Row keys
        if (is_array($this->row)) {
            $keys = array_keys($this->row);
        } else {
            $this->seterror();
            return false;
        }


        // Danner array med de n�gler, der findes i databasen
        foreach ($keys as $k) {
            if (in_array($k, $fields)) {
                $proved_row[$k] = $this->row[$k];
            }
        }

        // Inds�tter v�rdier i databasen
        $debug = $this->insert($proved_row, $table, $showsql, $utf8, $returnid, $returnsql_noexec);
        return $debug;
    }

    /**
     * T�mmer arrayet row
     *
     */
    public function resetrow() {
        $this->row = null;
    }

    /**
     * Erstatter row med indholdet af et array
     *
     * @param array $array
     */
    private function array_to_row($array) {
        $this->resetrow();
        $this->row = $array;
    }

    /**
     * Henter r�kker baseret p� kolonne og v�rdi
     *
     * @param var $col
     * @param var $value
     * @return array
     */
    public function get_byval($col, $value) {
        $array = $this->get("*", "", "`$col`='$value'");
        return $array;
    }

    /**
     * Henter r�kke baseret p� id
     *
     * @param int $id
     * @return array
     */
    public function get_byid($id) {
        $array = $this->get("*", "", "`id`='$id'", "", 1);
        return $array;
    }

    /**
     * Henter f�rste r�kke, baseret p� kolonne og v�rdi
     *
     * @param var $col
     * @param var $value
     * @return array
     */
    public function getfirst_byval($col, $value) {
        $array = $this->get("*", "", "`$col`='$value'", "", 1);
        return $array;
    }

    /**
     * Henter r�kker baseret p� kolonne og LIKE %v�rdi%
     *
     * @param var $col
     * @param var $value
     * @return array
     */
    public function get_likeval($col, $value) {
        $array = $this->get("*", "", "`$col` LIKE '%$value%'");
        return $array;
    }

    /**
     * Henter f�rste r�kke baseret p� kolonne og LIKE %v�rdi%
     *
     * @param var $col
     * @param var $value
     * @return array
     */
    public function getfirst_likeval($col, $value) {
        $array = $this->get("*", "", "`$col` LIKE '%$value%'", "", 1);
        return $array;
    }

}